package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.WaterCharge;

/**
 * A class used for managing water charge
 *
 */
public class WaterChargeDAO {

	/** a database of this water charge */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public WaterChargeDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all water charge in database
     * @return all water charge
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM water_charge";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findCalPrepareData(int pnSiteId, String paCustomerClass) {
        String vaSql = "SELECT site_id, customer_class, water_used_start, water_used_end, water_rate, water_base_consumption, "
        		+ " water_base_charge FROM water_charge WHERE site_id = ? AND customer_class = ? ORDER BY water_base_consumption ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId , paCustomerClass);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public Map<String, Object> findMinWaterBaseConsumption(int pnSiteId, String paCustomerClass) {
        String vaSql = "SELECT MIN(water_base_consumption) AS WATER_BASE_CONSUMPTION FROM water_charge WHERE site_id = ? AND customer_class = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId , paCustomerClass);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    // Louis 22-10-2557
    public List<Map<String, Object>> findCalPrepareData_Decimal2Digits(int pnSiteId, String paCustomerClass) {
        String vaSql = "SELECT site_id, customer_class, water_used_start, water_used_end, "
        		+ " TO_CHAR(ROUND(water_rate,2),'9,999,999.99') as water_rate, "
        		+ " water_base_consumption, "
        		+ " TO_CHAR(ROUND(water_base_charge,2),'9,999,999.99') as water_base_charge "
        		+ " FROM water_charge WHERE site_id = ? AND customer_class = ? " 
        		+ " ORDER BY water_used_start asc, water_used_end asc ";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId , paCustomerClass);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    // Louis 24-10-2557
    public List<Map<String, Object>> findWaterToWaterEnd(int pnSiteId, String paCustomerClass, int pnWaterTo, int paWaterEnd) {
        String vaSql = "SELECT site_id, customer_class, water_used_start, water_used_end, "
        		+ " TO_CHAR(ROUND(water_rate,2),'9,999,999.99') as water_rate, "
        		+ " water_base_consumption, "
        		+ " TO_CHAR(ROUND(water_base_charge,2),'9,999,999.99') as water_base_charge "
        		+ " FROM water_charge WHERE site_id = ? AND customer_class = ? "
        		+ " AND WATER_USED_START = ? AND WATER_USED_END = ? " 
        		+ " ORDER BY water_used_start asc, water_used_end asc ";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId , paCustomerClass, pnWaterTo, paWaterEnd);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    // Louis 24-10-2557
    public boolean Insert(WaterCharge waterCharge)
    {    	
		try {
			String vaSql = " INSERT INTO WATER_CHARGE ";
			vaSql += " (SITE_ID,CUSTOMER_CLASS,WATER_USED_START,WATER_USED_END,WATER_RATE,WATER_BASE_CONSUMPTION,WATER_BASE_CHARGE, ";
			vaSql += " INSERT_USER_ID,INSERT_DATE,INSERT_TIME,UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME) ";
			vaSql += " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ";

			db.insert(vaSql,
					waterCharge.getVnSiteId(),
					waterCharge.getVaCustomerClass(),
					waterCharge.getVnWaterUsedStart(),
					waterCharge.getVnWaterUsedEnd(),
					waterCharge.getVnWaterRate(),
					waterCharge.getVnWaterBaseConsumption(),
					waterCharge.getVnWaterBaseCharge(),
					waterCharge.getVnInsertUserId(),
					waterCharge.getVaInsertDate(),
					waterCharge.getVaInsertTime(),
					waterCharge.getVnUpdateUserId(),
					waterCharge.getVaUpdateDate(),
					waterCharge.getVaUpdateTime());
			return true;
		} catch (Exception e) {
			return false;
		}
    }
    
    // Louis 24-10-2557
    public boolean update(WaterCharge waterCharge, String paOldCustomerClass,
    		int paOldWaterStart, int pnOldWaterEnd)
    {
    	try {
			String vaSql = " update WATER_CHARGE set ";
			vaSql += " CUSTOMER_CLASS = ?, ";
			vaSql += " WATER_USED_START = ?, ";
			vaSql += " WATER_USED_END = ?, ";
			vaSql += " WATER_RATE = ?, ";
			vaSql += " WATER_BASE_CONSUMPTION = ?, ";
			vaSql += " WATER_BASE_CHARGE = ?, ";
			vaSql += " UPDATE_USER_ID = ?, ";
			vaSql += " UPDATE_DATE = ?, ";
			vaSql += " UPDATE_TIME = ? ";
			vaSql += " where SITE_ID = ? ";
			vaSql += " AND CUSTOMER_CLASS = ? ";
			vaSql += " AND WATER_USED_START = ? ";
			vaSql += " AND WATER_USED_END = ? ";

			db.update(vaSql,
					waterCharge.getVaCustomerClass(),
					waterCharge.getVnWaterUsedStart(),
					waterCharge.getVnWaterUsedEnd(),
					waterCharge.getVnWaterRate(),
					waterCharge.getVnWaterBaseConsumption(),
					waterCharge.getVnWaterBaseCharge(),
					waterCharge.getVnUpdateUserId(),
					waterCharge.getVaUpdateDate(),
					waterCharge.getVaUpdateTime(),
					waterCharge.getVnSiteId(),
					paOldCustomerClass,
					paOldWaterStart,
					pnOldWaterEnd);
			return true;
		} catch (Exception e) {
			return false;
		}
    }
    
    // Louis 28-10-2557
    public boolean delete(int vnSiteId, String paCustomerClass, int pnWaterUsedStart, int pnWaterUsedEnd)
    {
    	try {
			String vaSql = " delete from WATER_CHARGE ";
			vaSql += " where CUSTOMER_CLASS = ? ";
			vaSql += " and WATER_USED_START = ? ";
			vaSql += " and WATER_USED_END = ? ";
			vaSql += " and SITE_ID = ? ";

			db.update(vaSql, paCustomerClass, pnWaterUsedStart, pnWaterUsedEnd, vnSiteId);
			
			return true;
		} catch (Exception e) {
			return false;
		}
    }
    
    // Louis 30-10-2557
    public boolean isDuplicate(int pnSiteId, String paCustomerClass, int pnWaterTo, int pnWaterEnd) {
    	
    	//check water_used_start
        String vaSql = "SELECT site_id, customer_class, water_used_start, water_used_end, "
        		+ " TO_CHAR(ROUND(water_rate,2),'9,999,999.99') as water_rate, "
        		+ " water_base_consumption, "
        		+ " TO_CHAR(ROUND(water_base_charge,2),'9,999,999.99') as water_base_charge "
        		+ " FROM water_charge WHERE site_id = ? AND customer_class = ? "
        		+ " AND WATER_USED_START <= ? AND WATER_USED_END >= ? " 
        		+ " ORDER BY water_used_start asc, water_used_end asc ";
        
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId , paCustomerClass, pnWaterTo, pnWaterEnd);
        
        // Duplicate
        if (result != null && result.size() > 0) 
        {
            return true;
        } 
        else 
        {
        	//check water_used_end
            vaSql = "SELECT site_id, customer_class, water_used_start, water_used_end, "
            		+ " TO_CHAR(ROUND(water_rate,2),'9,999,999.99') as water_rate, "
            		+ " water_base_consumption, "
            		+ " TO_CHAR(ROUND(water_base_charge,2),'9,999,999.99') as water_base_charge "
            		+ " FROM water_charge WHERE site_id = ? AND customer_class = ? "
            		+ " AND WATER_USED_START <= ? AND WATER_USED_END >= ? " 
            		+ " ORDER BY water_used_start asc, water_used_end asc ";
            
            result = db.queryList(vaSql, pnSiteId , paCustomerClass, pnWaterTo, pnWaterEnd);
            
            // Duplicate
            if (result != null && result.size() > 0) 
            {
                return true;
            } 
            else 
            {
            	return false;
            }
        }
    }
}
